package com.leadership.api.dao;

import java.util.List;
import com.demo2do.core.persistence.annotation.Dao;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;
import com.leadership.training.entity.project.TrainingSurveyResult;
/**
 * 
 * @author Rockett
 *
 */
@Dao("hibernate")
public interface TrainingSurveyResultDao {
	
	/**
	 * @param traineeId
	 * @param projectPhaseId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult WHERE projectPhase.id =:projectPhaseId and trainee.id =:traineeId and projectPhaseCourse is null")
	public List<TrainingSurveyResult> listByTraineeAndProjectPhase(@Variable("traineeId") Long traineeId,@Variable("projectPhaseId") Long projectPhaseId);
	/**
	 * @param traineeId
	 * @param projectPhaseId
	 * @param projectPhaseCourseId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult WHERE projectPhase.id =:projectPhaseId and trainee.id =:traineeId and projectPhaseCourse.id =:projectPhaseCourseId")
	public List<TrainingSurveyResult> listByTraineeAndProjectPhaseAndCourse(@Variable("traineeId") Long traineeId,@Variable("projectPhaseId") Long projectPhaseId,@Variable("projectPhaseCourseId") Long projectPhaseCourseId);
	/**
	 * 
	 * @param projectPhaseId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult WHERE projectPhase.id =:projectPhaseId and projectPhaseCourse.id = null" )
	public List<TrainingSurveyResult> listByProjectPhase(@Variable("projectPhaseId") Long projectPhaseId);
	/**
	 * 
	 * @param courseId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult t WHERE exists( from ProjectPhaseCourse c where t.projectPhaseCourse.id = c.id and c.course.id =:courseId)" )
	public List<TrainingSurveyResult> listByCourse(@Variable("courseId") Long courseId);
	/**
	 * 
	 * @param projectPhaseCourseId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult t WHERE t.projectPhaseCourse.id =:projectPhaseCourseId" )
	public List<TrainingSurveyResult> listByProjectPhaseCourse(@Variable("projectPhaseCourseId") Long projectPhaseCourseId);
	/**
	 * 
	 * @param projectId
	 * @return
	 */
	@Query("select count(*) FROM TrainingSurveyResult t WHERE exists( from ProjectPhase p where t.projectPhase.id = p.id and p.project.id =:projectId)" )
	public int countByProject(@Variable("projectId") Long projectId);

	/**
	 * 
	 * @param trainerId
	 * @return
	 */
	@Query("select AVG(t.inputValue) FROM TrainingSurveyResult t WHERE (t.question like '%主题课程是否满足您的期望、对您的管理工作有所帮助？%' or t.question like '%授课逻辑清晰易懂，呈现演绎生动有趣，能够吸引您积极投入对主题内容的学习？%' or t.question like '%对主题内容有深度的理解，善于联系真实的工作场景与实际的管理挑战%' or t.question like '%主题课程进行整体评价%' ) and t.projectPhaseCourse.trainer.id =:trainerId and t.inputValue is not null and t.inputValue !='' and t.inputValue !='NA' and t.inputValue <6 " )
	public Double avgByTrainer(@Variable("trainerId") Long trainerId);
	/**
	 * 
	 * @param trainerId
	 * @return
	 */
	@Query("FROM TrainingSurveyResult t WHERE t.projectPhaseCourse.trainer.id =:trainerId and t.inputValue is not null and t.inputValue !='' and t.inputValue !='NA' " )
	public List<TrainingSurveyResult> listScoreByTrainer(@Variable("trainerId") Long trainerId);
	
	
}
